package com.shujia

import java.sql.{Connection, DriverManager, PreparedStatement}
import scala.io.{BufferedSource, Source}

object Demo7StudentToMysql {
  def main(args: Array[String]): Unit = {
    //1、读取文件
    val bufferedSource: BufferedSource = Source.fromFile("scala/data/students.csv")
    val iterable: Iterator[String] = bufferedSource.getLines()
    val students: List[String] = iterable.toList

    //2、创建数据库连接
    Class.forName("com.mysql.jdbc.Driver")
    val con: Connection = DriverManager.getConnection("jdbc:mysql://master:3306/bigdata27?useUnicode=true&characterEncoding=UTF-8&useSSL=false", "root", "123456")
    //编写插入sql
    val stat: PreparedStatement = con.prepareStatement("insert into students values(?,?,?,?,?)")

    //3、循环将数据保存到数据库中
    for (student <- students) {
      val split: Array[String] = student.split(",")
      val id: String = split(0)
      val name: String = split(1)
      val age: Int = split(2).toInt
      val sex: String = split(3)
      val clazz: String = split(4)

      //按照顺序给sql赋值
      stat.setString(1, id)
      stat.setString(2, name)
      stat.setInt(3, age)
      stat.setString(4, sex)
      stat.setString(5, clazz)

      //执行sql
      stat.execute()
    }

    //关闭连接
    con.close()
  }

}
